/**
 * 
 */
package com.laozhao.dreamframe.dbmanager;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.laozhao.dreamframe.model.DreamModel;
import com.laozhao.dreamframe.model.ModelUtil;


/**
 * @author LaoZhao
 *
 */
public class DataBaseManager {	

	private transient Log log = LogFactory.getLog(this.getClass());

	/**
	 * 连接类型为 oracle
	 */
	public final static String DATATYPE_ORACLE9 = "oracle9"; 
	/**
	 * 连接类型为 mysql
	 */
	public final static String DATATYPE_MYSQL = "mysql"; 
	/**
	 * 连接类型为本地 acess
	 */
	public final static String DATATYPE_ACCESS = "access"; 
	
	private Connection con;
//	private ResultSet rs;
	private Statement stmt;
//	private DriverManager dm;
//	
//	private String port;
	private String dbUrl;
	private String userName;
	private String passWorld;
//	private String serverName;

	/**
	 * init data base manager
	 * @param dataType
	 * @param dbUrl
	 * @param userName
	 * @param passWorld
	 * @param serverName
	 * @throws Exception
	 */
	public DataBaseManager(String dataType,String dbUrl,String userName,String passWorld,String serverName) throws Exception {
		log.debug("Create Data Base Manager start.");
		try {
			this.userName = userName;
			this.passWorld = passWorld;
//			this.dbUrl = dbUrl;
//			this.serverName = serverName;
			
			String typePrefix = dataType.toLowerCase();
			if(typePrefix.startsWith(DATATYPE_ORACLE9)){

				/**
				 * oracle db
				 */
				Class.forName("oracle.jdbc.driver.OracleDriver");
				this.dbUrl = dbUrl + ":" + serverName;
//				con = DriverManager.getConnection(dbUrl,userName,passWorld);
			}else if(typePrefix.startsWith(DATATYPE_MYSQL)){ 
				/**
				 * mysql db
				 */
				Class.forName("com.mysql.jdbc.Driver");
				this.dbUrl  = dbUrl +  "/" + serverName;
//				con = DriverManager.getConnection(dbUrl,userName,passWorld);
			}else if(typePrefix.startsWith(DATATYPE_ACCESS)){
				/**
				 * access connect
				 */
				Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
				this.dbUrl = dbUrl;
			}
			log.info("Create Data Base Manager success.");
		}catch (ClassNotFoundException cnfex) {
			log.error("Failed to load JDBC/ODBC driver.");
			log.debug("Failed Message", cnfex);
			cnfex.printStackTrace();
			System.exit(1); // terminate program
		}
	}

	/**
	 * open connect
	 * @return
	 * @throws SQLException
	 */
	public Connection openConnect() throws SQLException{
		log.debug("open connect start ");
		getExistConnect();
		if (con == null && !con.isClosed() ) {
			con = DriverManager.getConnection(dbUrl,userName,passWorld);
			stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			log.info("Open connect success");
		}
		return con;
	}
	
	/**
	 * close connect
	 * @throws SQLException
	 */
	public void closeConnection() throws SQLException{
		log.debug("close econnect start " );
		if (con != null && !con.isClosed()) {
			con.close();
		}
		if (stmt != null && !stmt.isClosed()) {
			stmt.close();
		}
		log.info("close econnect  success ");
	}
	
	/**
	 * get search result by searchStrSQL
	 * @param searchStrSQL
	 * @return
	 * @throws SQLException 
	 */
	public ResultSet getResult(String searchStrSQL) throws SQLException {
		log.debug("to exec sql query start " + searchStrSQL);
		ResultSet rs = stmt.executeQuery(searchStrSQL);
		log.info("exec sql query success ");
		return rs;
	}
	
	/**
	 * get search result by searchStrSQL and model class
	 * @param searchStrSQL
	 * @param clazz
	 * @return
	 * @throws SQLException
	 */
	public <T extends DreamModel> List<T> getResult(String searchStrSQL,Class<T> clazz) throws SQLException {
		log.debug("to exec sql query start " + searchStrSQL);
		ResultSet rs = stmt.executeQuery(searchStrSQL);
		log.info("exec sql query success ");
		
		return ModelUtil.Relection(rs, clazz);

	}

	/**
	 * get search result by searchStrSQL
	 * @param searchStrSQL
	 * @return
	 * @throws SQLException 
	 */
	public <T extends DreamModel> List<T> getAllResult(Class<T> clazz) throws SQLException {
		log.debug("getAllResult sql query start ");
		String sqlString = ModelUtil.getSelStr(clazz);
		log.debug(sqlString);
		ResultSet rs = stmt.executeQuery(sqlString);
		log.info("exec sql query success ");
		return ModelUtil.Relection(rs, clazz);
	}
	
	/**
	 * update db and auto commit by strsql
	 * @param strSQL
	 * @return
	 * @throws SQLException
	 */
	public int updateSql(String strSQL) throws SQLException {
		log.debug("to exec sql query start " + strSQL);
		int count = stmt.executeUpdate(strSQL);
		con.commit();
		log.info("exec sql query success ");
		return count;
	}

	/**
	 * update db and commit by strsql and isCommit
	 * @param strSQL
	 * @param comment
	 * @return
	 * @throws SQLException
	 */
	public int updateSql(String strSQL,boolean isCommit) throws SQLException {
		log.debug("to exec sql query start " + strSQL);
		int count = stmt.executeUpdate(strSQL);
		if(isCommit){
			con.commit();
		}
		log.info("exec sql query success ");
		return count;
	}
	
	/**
	 * conmmit
	 * @throws SQLException
	 */
	public void commit() throws SQLException{
		if (con != null && !con.isClosed()) {
			con.commit();
		}
	}

	public void rollback() throws SQLException {
		if (con != null && !con.isClosed()) {
			con.rollback();
		}
	}
	private Connection getExistConnect(){
		log.debug("open connect start ");
		try {
			if (!con.isClosed()) {
				return con;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			log.error("Connect is closed");
			log.debug("Failed message:", e);
		}
		return null;
	}
	
}

